package com.faithbj.utils;

import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;

import com.faithbj.model.conditions.CheckFamilyStatResult;
import com.faithbj.model.conditions.CompareStatData;
import com.faithbj.model.conditions.CompareStatResult;
import com.faithbj.model.conditions.ReportStatResult;
import com.faithbj.model.conditions.StatExcelHeaderData;
import com.faithbj.model.conditions.StatExcelHeaderDataModel;
import com.faithbj.model.conditions.StreetCompareStatData;
import com.faithbj.model.conditions.WorkInfoStatResult;

/**
 * Excel工具类
 * 根据统计分析中特定的数据格式生成Excel，list<String/list<>>
 * @author lihao
 *
 */
public class StatisticsExcelUtil {

	private static final Log log = LogFactory.getLog(StatisticsExcelUtil.class);
	
	/**
	 * 创建header样式
	 * 
	 * @param workbook
	 * @return
	 */
	private static HSSFCellStyle createHeaderStyle(HSSFWorkbook workbook) {
		HSSFFont font = workbook.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setFont(font);
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中    
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中    
		return cellStyle;
	}
	
	/**
	 * 创建Cell样式
	 * @param workbook
	 * @return
	 */
	private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook) {
		HSSFFont font = workbook.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setFont(font);
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中    
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中    
		return cellStyle;
	}
	
	/**
	 * excel header格式：ArrayList<String/ExcelData>
	 */
	private static void buildHeader(HSSFWorkbook workbook, HSSFSheet sheet, List<Object> header, int startRowNum) {
		if(header==null || header.size()==0)
			return;
		
		int headerRowNum = (Integer) header.get(0);
		for(int i=startRowNum; i<startRowNum+headerRowNum; i++) {
			sheet.createRow(i);
			sheet.autoSizeColumn(i);
		}
		HSSFCellStyle cellStyle = StatisticsExcelUtil.createHeaderStyle(workbook);// 创建样式
		int column = 0;
		for(int i=1; i<header.size(); i++) {
			Object obj = header.get(i);
			if(obj instanceof String) {
				HSSFCell cell = sheet.getRow(startRowNum).createCell(column);
				cell.setCellStyle(cellStyle);
				cell.setCellValue((String) obj);
				sheet.addMergedRegion(new Region(startRowNum, (short)column, startRowNum+headerRowNum-1, (short)column));//垂直方向
				column++;
			}else if(obj instanceof StatExcelHeaderDataModel) {
				StatExcelHeaderDataModel ed = (StatExcelHeaderDataModel) obj;
				int cols = ed.getSub().size();
				HSSFCell parent = sheet.getRow(startRowNum).createCell(column);
				parent.setCellStyle(cellStyle);
				parent.setCellValue(ed.getParent());
				sheet.addMergedRegion(new Region(startRowNum, (short)column, startRowNum, (short)(column+cols-1)));//水平方向
				for(int j=0; j<cols; j++) {
					HSSFCell sub = sheet.getRow(startRowNum+1).createCell(column);
					sub.setCellStyle(cellStyle);
					sub.setCellValue(ed.getSub().get(j));
					column++;
				}
			}
		}
	}
	
	/**
	 * 
	 * @param header 第一个数据为该list中一个条数据所占行数
	 * @param source 最后一个为合计数据
	 * @param sheetName
	 * @param response
	 */
	public static void exportExcel(List<Object> source, String sheetName, HttpServletResponse response) {
		List<Object> header = null;
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet();
		workbook.setSheetName(0, sheetName);
		
		int startRowNum = 0;

		try{
			if(source!=null && source.size()>0) {
				if(source.get(0) instanceof CheckFamilyStatResult) {
					header = StatExcelHeaderData.checkFamilyStatHeader;
				}else if(source.get(0) instanceof CompareStatResult) {
					header = StatExcelHeaderData.compareStatHeader;
				}else if(source.get(0) instanceof WorkInfoStatResult) {
					header = StatExcelHeaderData.workInfoStatHeader;
				}else if(source.get(0) instanceof ReportStatResult) {
					header = StatExcelHeaderData.reportStatHeader;
				}
					
				StatisticsExcelUtil.buildHeader(workbook, sheet, header, startRowNum);
				if(header!=null && header.size()>0)
					startRowNum += (Integer) header.get(0);
				if(source.get(0) instanceof CompareStatResult) {
					buildCompareStatExcel(workbook, sheet, source, startRowNum);
				}else {
					buildExcel(workbook, sheet, source, startRowNum);
				}
			}else {
				log.info("没有数据");
				}
			
			response.addHeader("Content-Disposition",
					"attachment; filename=results.xls");
		    response.setCharacterEncoding("utf-8");
			response.setContentType("application/vnd.ms-excel");
			workbook.write(response.getOutputStream());
			response.getOutputStream().flush();
			
	        FileOutputStream fileOut;
			fileOut = new   FileOutputStream( "F:/workbook.xls ");
	        workbook.write(fileOut); 
	        fileOut.close();
		} catch (SecurityException e) {
			e.printStackTrace();
			log.error(e.getMessage());
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
			log.error(e.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			log.error(e.getMessage());
		} catch (IllegalAccessException e) {
			e.printStackTrace();
			log.error(e.getMessage());
		} catch (IOException e) {
			e.printStackTrace();
			log.error(e.getMessage());
		} finally {
			try {
				response.getOutputStream().close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 为交换对比构建excel
	 * @param workbook
	 * @param sheet
	 * @param source
	 * @param startRowNum
	 * @throws SecurityException
	 * @throws IllegalArgumentException
	 * @throws ClassNotFoundException
	 * @throws IllegalAccessException
	 */
	private static void buildCompareStatExcel(HSSFWorkbook workbook,
			HSSFSheet sheet, List<Object> source, int startRowNum) throws SecurityException, IllegalArgumentException, ClassNotFoundException, IllegalAccessException {
		HSSFCellStyle cellStyle = StatisticsExcelUtil.createCellStyle(workbook);// 创建样式
		
		/**注:compareNum为比对数量，按户或人比对数量为1，两者全部比对数量为2*/
		int compareNum = 	((CompareStatResult) source.get(0)).getStreetCompareStatdataList().get(0).getCompareStatDataList().size();
		for(int i=0; i<source.size(); i++) {
			int column = 0;
			CompareStatResult cs = (CompareStatResult) source.get(i);
			
			int rowNum = cs.getStreetCompareStatdataList().size() * compareNum;
			//创建行
			for(int m=0; m<rowNum; m++) {
				sheet.createRow(startRowNum+m);
			}
			
			//设置区县单元格
			HSSFCell county = sheet.getRow(startRowNum).createCell(column);
			county.setCellStyle(cellStyle);
			county.setCellType(HSSFCell.CELL_TYPE_STRING);
			county.setCellValue(cs.getCountryRegionName());
			sheet.addMergedRegion(new Region(startRowNum, (short)column, startRowNum+rowNum-1, (short)column));
			column++;
			
			//设置街道数据
			for(int j=0; j<cs.getStreetCompareStatdataList().size(); j++) {
				StreetCompareStatData sc = cs.getStreetCompareStatdataList().get(j);
				//设置街道单元格
				HSSFCell street = sheet.getRow(startRowNum).createCell(column);
				street.setCellStyle(cellStyle);
				street.setCellType(HSSFCell.CELL_TYPE_STRING);
				street.setCellValue(sc.getStreetRegionName());
				sheet.addMergedRegion(new Region(startRowNum, (short)column, startRowNum+compareNum-1, (short)column));
				
				if(compareNum == 1) {
					buildOneRowOfCompare(workbook, sheet,  sc.getCompareStatDataList().get(0), startRowNum, column+1);
					startRowNum++;
				}else if(compareNum == 2) {
					//先按户数后按人数
					CompareStatData csd1 = sc.getCompareStatDataList().get(0);
					CompareStatData csd2 = sc.getCompareStatDataList().get(1);
					if(csd1.getType() == 1) {
						buildOneRowOfCompare(workbook, sheet,  csd1, startRowNum, column+1);
						startRowNum++;
						buildOneRowOfCompare(workbook, sheet,  csd2, startRowNum, column+1);
						startRowNum++;
					}else {
						buildOneRowOfCompare(workbook, sheet,  csd2, startRowNum, column+1);
						startRowNum++;
						buildOneRowOfCompare(workbook, sheet,  csd1, startRowNum, column+1);
						startRowNum++;
					}
				}
			}
		}
	}

	/**
	 * 为其他统计构建excel
	 * @param workbook
	 * @param sheet
	 * @param source
	 * @param startRowNum
	 * @throws SecurityException
	 * @throws ClassNotFoundException
	 * @throws IllegalArgumentException
	 * @throws IllegalAccessException
	 */
	private static void buildExcel(HSSFWorkbook workbook,
			HSSFSheet sheet, List<Object> source, int startRowNum) throws SecurityException, ClassNotFoundException, IllegalArgumentException, IllegalAccessException {
		HSSFCellStyle cellStyle = StatisticsExcelUtil.createCellStyle(workbook);// 创建样式
		
		for(int i=0; i<source.size(); i++) {
			int column = 0;
			Object obj = source.get(i);
			
			int rowNum = 0;
			Field[] fields = Class.forName(obj.getClass().getName()).getDeclaredFields();
			if(fields!=null && fields.length>0) {
				for(Field field : fields) {
					if(field!= null && !field.equals("")) {
						field.setAccessible(true);
						if(field.getType().equals(List.class))
							rowNum = ((List) field.get(obj)).size();
					}
				}
			}
			
			//创建行
			for(int m=0; m<rowNum; m++) {
				sheet.createRow(startRowNum+m);
			}
			
			if(fields!=null && fields.length>0) {
				for(Field field : fields) {
					if(field!= null && !field.equals("")) {
						field.setAccessible(true);
						if(field.getType().equals(String.class)) {
							//设置区县单元格
							HSSFCell county = sheet.getRow(startRowNum).createCell(column);
							county.setCellStyle(cellStyle);
							county.setCellType(HSSFCell.CELL_TYPE_STRING);
							county.setCellValue(field.get(obj).toString());
							sheet.addMergedRegion(new Region(startRowNum, (short)column, startRowNum+rowNum-1, (short)column));
							column++;
						}else if(field.getType().equals(List.class)) {
							//设置街道单元格
							for(int j=0; j<rowNum; j++) {
								buildOneRow(workbook, sheet,  ((List) field.get(obj)).get(j), startRowNum, column);
								startRowNum++;
							}
						}
					}
				}
			}
		}
	}
	
	/**
	 * 
	 * @param workbook
	 * @param sheet
	 * @param obj 表格中一行的数据
	 * @param startRowNum 起始行
	 * @param column 起始列
	 * @throws ClassNotFoundException 
	 * @throws SecurityException 
	 * @throws IllegalAccessException 
	 * @throws IllegalArgumentException 
	 */
	private static void buildOneRow(HSSFWorkbook workbook, HSSFSheet sheet,
			Object obj, int startRowNum, int column) throws SecurityException, ClassNotFoundException, IllegalArgumentException, IllegalAccessException {
		HSSFCellStyle cellStyle = StatisticsExcelUtil.createCellStyle(workbook);// 创建样式
		
		Field[] fields = Class.forName(obj.getClass().getName()).getDeclaredFields();
		if(fields!=null && fields.length>0) {
			for(Field field : fields) {
				if(field!= null && !field.equals("")) {
					field.setAccessible(true);
					if (field.getType().equals(String.class)) {
						HSSFCell cell = sheet.getRow(startRowNum).createCell(column);
						cell.setCellStyle(cellStyle);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(field.get(obj).toString());
						column++;
					} else if (field.getType().equals(int.class)) {
						HSSFCell cell = sheet.getRow(startRowNum).createCell(column);
						cell.setCellStyle(cellStyle);
						cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
						cell.setCellValue(field.getInt(obj));
						column++;
					} else if (field.getType().equals(long.class)) {
						HSSFCell cell = sheet.getRow(startRowNum).createCell(column);
						cell.setCellStyle(cellStyle);
						cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
						cell.setCellValue(field.getLong(obj));
						column++;
					}else {
						HSSFCell cell = sheet.getRow(startRowNum).createCell(column);
						cell.setCellStyle(cellStyle);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(field.get(obj).toString());
						column++;
					}
				}
			}
		}
	}
	/**
	 * 为交换对比中一条数据构建一行
	 * @param workbook
	 * @param sheet
	 * @param csd
	 * @param startRowNum
	 * @param column
	 * @throws SecurityException
	 * @throws ClassNotFoundException
	 * @throws IllegalArgumentException
	 * @throws IllegalAccessException
	 */
	private static void buildOneRowOfCompare(HSSFWorkbook workbook, HSSFSheet sheet,
			CompareStatData csd, int startRowNum, int column) throws SecurityException, ClassNotFoundException, IllegalArgumentException, IllegalAccessException {
		HSSFCellStyle cellStyle = StatisticsExcelUtil.createCellStyle(workbook);// 创建样式
		HSSFCell cell = null;
		/**房产局已发送数*/
		cell = sheet.getRow(startRowNum).createCell(column);
		cell.setCellStyle(cellStyle);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(csd.getFangChanSendNum());
		column++;
		/**房产局已返回数据*/
		cell = sheet.getRow(startRowNum).createCell(column);
		cell.setCellStyle(cellStyle);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(csd.getFangChanRecvNum());
		column++;
		/**人社局已发送数*/
		cell = sheet.getRow(startRowNum).createCell(column);
		cell.setCellStyle(cellStyle);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(csd.getRensheSendNum());
		column++;
		/**人社局已返回数*/
		cell = sheet.getRow(startRowNum).createCell(column);
		cell.setCellStyle(cellStyle);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(csd.getRensheRecvNum());
		column++;
		/**地税局已发送数*/
		cell = sheet.getRow(startRowNum).createCell(column);
		cell.setCellStyle(cellStyle);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(csd.getDishuiSendNum());
		column++;
		/**地税局已返回数*/
		cell = sheet.getRow(startRowNum).createCell(column);
		cell.setCellStyle(cellStyle);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(csd.getDishuiRecvNum());
		column++;
		/**分安局已发送数*/
		cell = sheet.getRow(startRowNum).createCell(column);
		cell.setCellStyle(cellStyle);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(csd.getGonganSendNum());
		column++;
		/**分安局已返回数*/
		cell = sheet.getRow(startRowNum).createCell(column);
		cell.setCellStyle(cellStyle);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(csd.getGonganRecvNum());
		column++;
		/**工商局已发送数*/
		cell = sheet.getRow(startRowNum).createCell(column);
		cell.setCellStyle(cellStyle);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(csd.getGongshangSendNum());
		column++;
		/**工商局已返回数*/
		cell = sheet.getRow(startRowNum).createCell(column);
		cell.setCellStyle(cellStyle);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(csd.getGongshangRecvNum());
		column++;
		/**住房公积金管理中心已发送数*/
		cell = sheet.getRow(startRowNum).createCell(column);
		cell.setCellStyle(cellStyle);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(csd.getGongjijinSendNum());
		column++;
		/**住房公积金管理中心已返回数*/
		cell = sheet.getRow(startRowNum).createCell(column);
		cell.setCellStyle(cellStyle);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(csd.getGongjijinRecvNum());
		column++;
		/**所有委办已发送数*/
		cell = sheet.getRow(startRowNum).createCell(column);
		cell.setCellStyle(cellStyle);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(csd.getSendNumSum());
		column++;
		/**所有委办已接收数*/
		cell = sheet.getRow(startRowNum).createCell(column);
		cell.setCellStyle(cellStyle);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(csd.getRecvNumSum());
		column++;
	}
}
